package com.yjd.bi.etl.sql;

import com.yjd.bi.comm.utils.DbNameUtils;

public class FundsSqlConstants {

	/**成交量统计**/
	//标的投资
	public final static String INVEST_SUMPRICE_SQL = "select IFNULL(sum(t.price), 0.00) as sumPrice from " + DbNameUtils.get("yjd_invest") + " t where t.addtime >= ? and t.addtime <= ?  and exists (select 1 from " + DbNameUtils.get("yjd_borrow") + " a where t.borrow_id = a.borrow_id and a.status >=40 ) ";
	//债权转让
	public final static String CREDITOR_SUMPRICE_SQL = "select IFNULL(sum(t.price), 0.00) as sumPrice from " + DbNameUtils.get("yjd_creditor_transfer") + " t where t.oktime >= ? and t.oktime <= ?";
	//小钱包
	public final static String WAL_PROJECT_SUMPRICE_SQL = "select IFNULL(sum(t.price), 0.00) as sumPrice from " + DbNameUtils.get("yjd_wal_project_invest") + " t where t.create_time >= ? and t.create_time <= ? and exists (select 1 from " + DbNameUtils.get("yjd_wal_project") + " a where t.wal_project_id = a.id and a.status = 2) ";

	/**充值金额**/
	public final static String RECHARGE_SUMPRICE_SQL = "SELECT IFNULL(sum(t.price), 0.00) AS recharge FROM " + DbNameUtils.get("yjd_order") + " t, " + DbNameUtils.get("yjd_user") + " u WHERE t.user_id = u.user_id and u.user_type &4!=4 AND t.`status` = 1   AND t.addtime >= ? AND t.addtime <= ?";

	/**充值金额 灵通卡 （小钱包）**/
	public final static String RECHARGE_SUMPRICE_WAL_SQL = "select IFNULL(sum(t.price), 0.00) as recharge from " + DbNameUtils.get("yjd_wal_funds_data") + " t where t.`type` = 2 and t.create_time >= ? and t.create_time <= ?";
	
	/**提现金额**/
	public final static String CASH_SUMPRICE_SQL = "SELECT IFNULL(sum(t.price), 0.00) AS cash FROM " + DbNameUtils.get("yjd_cash") + " t, " + DbNameUtils.get("yjd_user") + " u WHERE t.user_id = u.user_id and u.user_type &4!=4 AND t.`status` = 4 AND t.addtime >= ? AND t.addtime <= ?";

	/**存管账户提现金额**/
	public final static String CASH_DEPO_SUMPRICE_SQL = "SELECT IFNULL(sum(t.price), 0.00) AS cash FROM " + DbNameUtils.get("yjd_cash_dcs_order") + " t, " + DbNameUtils.get("yjd_user") + " u WHERE t.user_id = u.user_id and u.user_type &4!=4 AND t.`status` = 3 AND t.addtime >= ? AND t.addtime <= ?";

	/**可用余额(不包含借款人)**/
	public final static String AVAILABLE_SUMPRICE_SQL = "SELECT IFNULL(sum(a.balance), 0.00) AS available FROM " + DbNameUtils.get("yjd_funds_data") + " a, ( SELECT max(t.id) AS id FROM " + DbNameUtils.get("yjd_funds_data") + " t WHERE t.uptime <= ? GROUP BY t.user_id ) b, " + DbNameUtils.get("yjd_user") + " u WHERE a.user_id = u.user_id and u.user_type &4!=4 and a.id = b.id";
	/**可用余额（包含借款人）**/
	public final static String AVAILABLE_SUMPRICE_SQL_ALL = "SELECT IFNULL(sum(a.balance), 0.00) AS available FROM " + DbNameUtils.get("yjd_funds_data") + " a, ( SELECT max(t.id) AS id FROM " + DbNameUtils.get("yjd_funds_data") + " t WHERE t.uptime <= ? GROUP BY t.user_id ) b, " + DbNameUtils.get("yjd_user") + " u WHERE a.user_id = u.user_id  and a.id = b.id";

	
	/**可用余额(新增)**/
	public final static String ADD_AVAILABLE_SUMPRICE_SQL =" select  IFNULL(sum(a.price), 0.00) as available from " + DbNameUtils.get("yjd_funds_data") + " a  where a.income = 1 and uptime >= ? and uptime <= ? ";

	/**可用余额(减少)**/
	public final static String SUB_AVAILABLE_SUMPRICE_SQL =" select  IFNULL(sum(a.price), 0.00) as available from " + DbNameUtils.get("yjd_funds_data") + " a  where a.income = -1  and a.type !=-2 and uptime >= ? and uptime <= ? ";
	
	/**可用余额(新增 解冻)**/
	
	public final static String ADD_AVAILABLE_SUMPRICE_SQL_JD = "select  IFNULL(sum(a.price), 0.00) as available from " + DbNameUtils.get("yjd_funds_data") + " a  where  a.income = 0 and a.type in (103,101) and uptime >= ? and uptime <= ? "; 
	
	/**可用余额(减少 冻结)**/
	public final static String ADD_AVAILABLE_SUMPRICE_SQL_DJ = "select  IFNULL(sum(a.price), 0.00) as available from " + DbNameUtils.get("yjd_funds_data") + " a  where  a.income = 0 and a.type in (100,102) and uptime >= ? and uptime <= ? ";

	/**在贷金额**/
	//public final static String BORROW_SUMPRICE_SQL = "SELECT IFNULL(SUM(b.capital), 0.00)  as borrow FROM " + DbNameUtils.get("yjd_borrow") + " a, " + DbNameUtils.get("yjd_arrears_form") + " b WHERE a.borrow_id = b.borrow_id AND a.repay_starttime< ? AND ( b.status=0 OR  ( b.status=1 AND b.uptime>=? )) ";

	public final static String BORROW_SUMPRICE_SQL =" select sum(capital) as borrow FROM  "+DbNameUtils.get("yjd_arrears_form")+"  WHERE reday <= ? and `status` != 1 and capital > 0 ";
	/**新增汇总**/
	public final static String INSERT_FUNDSSTATISTICS_SQL = "insert into " + DbNameUtils.get("yjd_bi_funds_statistics") + "(`volume`,`recharge`,`cash`,`available`,`borrow`,`statistical_date`,`thaw_price`,`freeze_price`,`pay_price`,`income_price`,`create_time`,`lck_available`,`all_available`,`cash_depo`)values (?,?,?,?,?,?,?,?,?,?,unix_timestamp(),?,?,?)";

	
	/**修改汇总**/
	public final static String UPDATE_FUNDSSTATISTICS_SQL = "update " + DbNameUtils.get("yjd_bi_funds_statistics") + " set `volume`=? ,`recharge`=?,`cash`=?,`available`=?, `borrow`=?,`thaw_price`=?,`freeze_price`=?,`pay_price`=?,`income_price`=?,`create_time`=unix_timestamp(),`lck_available` = ? ,`all_available` = ?,`cash_depo`=? where id = ?";

	/**查询最新统计日期**/
	public final static String FUNDSSTATISTICS_MAXDATE = "select a.statistical_date as maxDate , IFNULL(a.available, 0.00) as  available from " + DbNameUtils.get("yjd_bi_funds_statistics") + " a where a.statistical_date = ( select max(t.statistical_date) from " + DbNameUtils.get("yjd_bi_funds_statistics") + " t) ";
	
	/**根据日期查询统计数据是否存在**/
	public final static String  EXIST_FUNDSSTATISTICS= " select id from " + DbNameUtils.get("yjd_bi_funds_statistics") + " t where t.statistical_date = ?";
	

	
	/**查询最新统计日期（30分钟统计）**/
	public final static String FUNDSSTATISTICS_MINUTE_MAXDATE = "select a.statistical_date as maxDate , IFNULL(a.available, 0.00) as  available from " + DbNameUtils.get("yjd_bi_funds_statistics_minute") + " a  where a.statistical_date = (select max(t.statistical_date) as maxDate from " + DbNameUtils.get("yjd_bi_funds_statistics_minute") + " t)  ";

	/**新增汇总（30分钟统计）**/
	public final static String INSERT_FUNDSSTATISTICS_MINUTE_SQL = "insert into " + DbNameUtils.get("yjd_bi_funds_statistics_minute") + "(`volume`,`recharge`,`cash`,`available`,`borrow`,`statistical_date`,`thaw_price`,`freeze_price`,`pay_price`,`income_price`,`create_time`)values (?,?,?,?,?,?,?,?,?,?,unix_timestamp())";
	
	/**修改汇总（30分钟统计）**/
	public final static String UPDATE_FUNDSSTATISTICS_MINUTE_SQL = "update " + DbNameUtils.get("yjd_bi_funds_statistics_minute") + " set `volume`=? ,`recharge`=?,`cash`=?,`available`=?, `borrow`=?,`thaw_price`=?,`freeze_price`=?,`pay_price`=?,`income_price`=?,`create_time`=unix_timestamp() where id = ?";
	
	
	/**根据日期查询统计数据是否存在(30分钟统计）**/
	public final static String  EXIST_FUNDSSTATISTICS_MINUTE= " select id from " + DbNameUtils.get("yjd_bi_funds_statistics_minute") + " t where t.statistical_date = ?";
	
	/**资金量统计，删除最后30天数据**/
	public static final String DELETE_FUNDS_STATISTIC = "delete from yjd_bi_funds_statistics where id in (select t.id from( select id from yjd_bi_funds_statistics s order by  s.statistical_date desc limit 30)t)";
	
	// 理财卡可用余额
	public static final String LCK_AVAILABLE = "SELECT IFNULL(SUM(b.card_balance), 0.00) AS available FROM (SELECT record_id, add_time, card_balance, card_no FROM (SELECT record_id, add_time, card_balance, card_no FROM yjd_card_record a1 where exists(select 1 from yjd_user_card c where c.card_no=a1.card_no ) and(a1.add_time <= ?) ORDER BY add_time DESC,record_id desc) a GROUP BY card_no) b";
}
